package project2;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.TreeSet;
import java.util.Vector;

public class MyFakebookOracle extends FakebookOracle {
	
	static String prefix = "paggarw.";
	
	// You must use the following variable as the JDBC connection
	Connection oracleConnection = null;
	
	// You must refer to the following variables for the corresponding tables in your database
	String cityTableName = null;
	String userTableName = null;
	String friendsTableName = null;
	String currentCityTableName = null;
	String hometownCityTableName = null;
	String programTableName = null;
	String educationTableName = null;
	String eventTableName = null;
	String participantTableName = null;
	String albumTableName = null;
	String photoTableName = null;
	String coverPhotoTableName = null;
	String tagTableName = null;
	
	
	// DO NOT modify this constructor
	public MyFakebookOracle(String u, Connection c) {
		super();
		String dataType = u;
		oracleConnection = c;
		// You will use the following tables in your Java code
		cityTableName = prefix+dataType+"_CITIES";
		userTableName = prefix+dataType+"_USERS";
		friendsTableName = prefix+dataType+"_FRIENDS";
		currentCityTableName = prefix+dataType+"_USER_CURRENT_CITY";
		hometownCityTableName = prefix+dataType+"_USER_HOMETOWN_CITY";
		programTableName = prefix+dataType+"_PROGRAMS";
		educationTableName = prefix+dataType+"_EDUCATION";
		eventTableName = prefix+dataType+"_USER_EVENTS";
		albumTableName = prefix+dataType+"_ALBUMS";
		photoTableName = prefix+dataType+"_PHOTOS";
		tagTableName = prefix+dataType+"_TAGS";
	}
	
	
	@Override
	// ***** Query 0 *****
	// This query is given to your for free;
	// You can use it as an example to help you write your own code
	//
	public void findMonthOfBirthInfo() throws SQLException{ 
		
		// Scrollable result set allows us to read forward (using next())
		// and also backward.  
		// This is needed here to support the user of isFirst() and isLast() methods,
		// but in many cases you will not need it.
		// To create a "normal" (unscrollable) statement, you would simply call
		// Statement stmt = oracleConnection.createStatement();
		//
		Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		// For each month, find the number of friends born that month
		// Sort them in descending order of count
		ResultSet rst = stmt.executeQuery("select count(*), month_of_birth from "+
				userTableName+
				" where month_of_birth is not null group by month_of_birth order by 1 desc");
		
		this.monthOfMostFriend = 0;
		this.monthOfLeastFriend = 0;
		this.totalFriendsWithMonthOfBirth = 0;
		
		// Get the month with most friends, and the month with least friends.
		// (Notice that this only considers months for which the number of friends is > 0)
		// Also, count how many total friends have listed month of birth (i.e., month_of_birth not null)
		//
		while(rst.next()) {
			int count = rst.getInt(1);
			int month = rst.getInt(2);
			if (rst.isFirst())
				this.monthOfMostFriend = month;
			if (rst.isLast())
				this.monthOfLeastFriend = month;
			this.totalFriendsWithMonthOfBirth += count;
		}
		
		// Get the names of friends born in the "most" month
		rst = stmt.executeQuery("select user_id, first_name, last_name from "+
				userTableName+" where month_of_birth="+this.monthOfMostFriend);
		while(rst.next()) {
			Long uid = rst.getLong(1);
			String firstName = rst.getString(2);
			String lastName = rst.getString(3);
			this.friendsInMonthOfMost.add(new UserInfo(uid, firstName, lastName));
		}
		
		// Get the names of friends born in the "least" month
		rst = stmt.executeQuery("select first_name, last_name, user_id from "+
				userTableName+" where month_of_birth="+this.monthOfLeastFriend);
		while(rst.next()){
			String firstName = rst.getString(1);
			String lastName = rst.getString(2);
			Long uid = rst.getLong(3);
			this.friendsInMonthOfLeast.add(new UserInfo(uid, firstName, lastName));
		}
		
		// Close statement and result set
		rst.close();
		stmt.close();
	}

	
	
	@Override
	// ***** Query 1 *****
	// Find information about friend names:
	// (1) The longest last name (if there is a tie, include all in result)
	// (2) The shortest last name (if there is a tie, include all in result)
	// (3) The most common last name, and the number of times it appears (if there is a tie, include all in result)
	//
	public void findNameInfo() throws SQLException { // Query1
	
		Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		ResultSet rst = stmt.executeQuery("select last_name, length(last_name), count(*) from "+
				userTableName+
				" where last_name is not null group by last_name order by 2 desc");
	       
		this.mostCommonLastNamesCount = 0;

		int longLen;
		int shortLen;
                           
                rst.last();
		shortLen = rst.getInt(2);
		rst.first();
		longLen = rst.getInt(2);

		rst.previous();

		while(rst.next())
		{
                	String lastName = rst.getString(1);
         		int length = rst.getInt(2);
			int count = rst.getInt(3);

			if(length == longLen)
				this.longestLastNames.add(lastName);
			if(length == shortLen)
				this.shortestLastNames.add(lastName);

			if(count > this.mostCommonLastNamesCount)
				this.mostCommonLastNamesCount = count;

		}

		rst.first();
		rst.previous();

		while(rst.next())
		{
                 	String lastName = rst.getString(1);
			int count = rst.getInt(3);

			if(count == this.mostCommonLastNamesCount)
			       	this.mostCommonLastNames.add(lastName); 
		}

         	// Close statement and result set
		rst.close();
		stmt.close();   

	}
	
	@Override
	// ***** Query 2 *****
	// Find the user(s) who have no friends in the network
	//
	// Be careful on this query!
	// Remember that if two users are friends, the friends table
	// only contains the pair of user ids once, subject to 
	// the constraint that user1_id < user2_id
	//
	public void lonelyFriends() throws SQLException {
		
		Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		ResultSet rst = stmt.executeQuery("select user_id, first_name, last_name from "+
				userTableName+
				" where user_id NOT IN (select user1_id from " + friendsTableName + 
				") AND user_id NOT IN (select user2_id from " + friendsTableName + ")");
		 
                int count = 0;

		while(rst.next())
		{                
			Long uid = rst.getLong(1);
			String firstName = rst.getString(2);
			String lastName = rst.getString(3);

			this.lonelyFriends.add(new UserInfo(uid, firstName, lastName));
			count++;
		}
		
		this.countLonelyFriends = count;

              	// Close statement and result set
		rst.close();
		stmt.close();   
 
	}
	 

	@Override
	// ***** Query 3 *****
	// Find the users who still live in their hometowns
	// (I.e., current_city = hometown_city)
	//	
	public void liveAtHome() throws SQLException {
		
		Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		ResultSet rst = stmt.executeQuery("select user_id, first_name, last_name from "+
				userTableName+ " utn " +
				" where (select current_city_id from " + currentCityTableName + " cctn where "
				+ "utn.user_id = cctn.user_id) = (select hometown_city_id from " + hometownCityTableName +
				" hctn where utn.user_id = hctn.user_id)");

		int count = 0;

		while(rst.next())
		{
			Long uid = rst.getLong(1);
			String firstName = rst.getString(2);
			String lastName = rst.getString(3);

			this.liveAtHome.add(new UserInfo(uid, firstName, lastName));
			count++;
		}
		
		this.countLiveAtHome = count;
        
	
		// Close statement and result set
		rst.close();
		stmt.close();   
	}



	@Override
	// **** Query 4 ****
	// Find the top-n photos based on the number of tagged users
	// If there are ties, choose the photo with the smaller numeric PhotoID first
	// 
	public void findPhotosWithMostTags(int n) throws SQLException { 
		
		Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		Statement stmt2 = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		Statement stmt3 = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		Statement stmt4 = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
	        ResultSet mostTags = stmt.executeQuery("select * from " +
	       	        	"(select tag_photo_id, count(tag_photo_id) from " + tagTableName +
	          	     	" group by tag_photo_id order by 2 desc, 1 asc) where rownum <= " + n);
		
                ResultSet photoInfo = null;
		ResultSet albumInfo = null;
		ResultSet tagInfo = null;

		while(mostTags.next())
		{
                        String tempId = mostTags.getString(1);

		        photoInfo = stmt2.executeQuery(
				"select photo_id, album_id, photo_caption, photo_link from " + 
				photoTableName + " where photo_id = " + tempId);
                        
			photoInfo.first();

               		String photoId = photoInfo.getString(1);
			String albumId = photoInfo.getString(2);
	       		String photoCaption = photoInfo.getString(3); 
	       		String photoLink = photoInfo.getString(4); 
                         
		        albumInfo = stmt3.executeQuery("select album_name from " + albumTableName + 
				" where album_id = " + albumId);

			albumInfo.first();

			String albumName = albumInfo.getString(1); 
			PhotoInfo p = new PhotoInfo(photoId, albumId, albumName, photoCaption, photoLink);
	       		TaggedPhotoInfo tp = new TaggedPhotoInfo(p);  	
	       	       	
	       		tagInfo = stmt4.executeQuery("select user_id, first_name, last_name from " 
	       			+ userTableName + " where user_id IN (select tag_subject_id from " +
				tagTableName + " where tag_photo_id = " + photoId + ")");

	       		while(tagInfo.next())
	       		{
	       			tp.addTaggedUser(new UserInfo(tagInfo.getLong(1), tagInfo.getString(2), 
	       				tagInfo.getString(3)));
	       		}
	       	
	       		this.photosWithMostTags.add(tp);  
	       }

               	// Close statement and result set
		tagInfo.close();
	        albumInfo.close();
		photoInfo.close();
	        mostTags.close();
		stmt.close();   
		stmt2.close();
		stmt3.close();
		stmt4.close();
	}

	
	
	
	@Override
	// **** Query 5 ****
	// Find suggested "match pairs" of friends, using the following criteria:
	// (1) One of the friends is female, and the other is male
	// (2) Their age difference is within "yearDiff"
	// (3) They are not friends with one another
	// (4) They should be tagged together in at least one photo
	//
	// You should up to n "match pairs"
	// If there are more than n match pairs, you should break ties as follows:
	// (i) First choose the pairs with the largest number of shared photos
	// (ii) If there are still ties, choose the pair with the smaller user_id for the female
	// (iii) If there are still ties, choose the pair with the smaller user_id for the male
	//
	public void matchMaker(int n, int yearDiff) throws SQLException { 
		
		int count = 0;
		
		Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		Statement stmt2 = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		Statement stmt3 = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		Statement stmt4 = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);
		
		
	        /*ResultSet matches = stmt.executeQuery("select u1.user_id, u2.user_id, count(t.tag_photo_id) " + 
				"from " + userTableName + " u1, " + userTableName + " u2, " + tagTableName + " t " + 
				" where u1.gender is not null AND u2.gender is not null AND " + "u1.gender = 'male' AND u2.gender = 'female' AND " +
				"NOT EXISTS(select user1_id, user2_id from " + friendsTableName + " where u1.user_id = user1_id AND u2.user_id = user2_id) AND " +
				"NOT EXISTS(select user1_id, user2_id from " + friendsTableName + " where u2.user_id = user1_id AND u1.user_id = user2_id) AND " +
				//"EXISTS((select tag_photo_id from " + tagTableName + " where tag_subject_id = u1.user_id) INTERSECT " +
				//"(select tag_photo_id from " + tagTableName + " where tag_subject_id = u2.user_id)) AND " +
				"u1.year_of_birth is not null AND u2.year_of_birth is not null AND ABS(u1.year_of_birth - u2.year_of_birth) <= " + yearDiff +
				"group by u1.user_id, u2.user_id");
                  */
	        ResultSet matches = stmt.executeQuery("select u1.user_id, u2.user_id, count(t.tag_photo_id) " + 
				"from " + userTableName + " u1, " + userTableName + " u2, " + tagTableName + " t " + 
                                "where t.tag_subject_id = u1.user_id AND EXISTS(select t2.tag_photo_id from " + tagTableName + 
				" t2 where t.tag_photo_id = t2.tag_photo_id AND t2.tag_subject_id = u2.user_id) " +
				" AND u1.gender is not null AND u2.gender is not null AND " + "u1.gender = 'male' AND u2.gender = 'female' AND " +
				"NOT EXISTS(select user1_id, user2_id from " + friendsTableName + " where u1.user_id = user1_id AND u2.user_id = user2_id) AND " +
				"NOT EXISTS(select user1_id, user2_id from " + friendsTableName + " where u2.user_id = user1_id AND u1.user_id = user2_id) AND " +
				"u1.year_of_birth is not null AND u2.year_of_birth is not null AND ABS(u1.year_of_birth - u2.year_of_birth) <= " + yearDiff + 
				" group by u1.user_id, u2.user_id order by 3 desc, 2 asc, 1 asc"); 
		
                ResultSet matchInfo = null;
		ResultSet photoInfo = null;
		ResultSet albumInfo = null;

                while(count < n && matches.next())
		{
			Long uid1 = matches.getLong(1);
			Long uid2 = matches.getLong(2); 
                        int photos_together = matches.getInt(3);

			if(photos_together == 0)
				break;
                        
			matchInfo = stmt2.executeQuery("select user_id, first_name, last_name, year_of_birth " +
						"from " + userTableName + " " +
						"where user_id = " + uid2);
                
                	matchInfo.next();

			Long girlUserId = matchInfo.getLong(1);
			String girlFirstName = matchInfo.getString(2);
			String girlLastName = matchInfo.getString(3);
			int girlYear = matchInfo.getInt(4);
		         
			matchInfo = stmt2.executeQuery("select user_id, first_name, last_name, year_of_birth " +
						"from " + userTableName + " " +
						"where user_id = " + uid1);
                	
			matchInfo.next();
                	
			Long boyUserId = matchInfo.getLong(1);
			String boyFirstName = matchInfo.getString(2);
			String boyLastName = matchInfo.getString(3);
			int boyYear = matchInfo.getInt(4);
	
			MatchPair mp = new MatchPair(girlUserId, girlFirstName, girlLastName, 
			                girlYear, boyUserId, boyFirstName, boyLastName, boyYear);
	 
         		photoInfo = stmt3.executeQuery("select photo_id, album_id, photo_caption, photo_link " +
						"from " + photoTableName + " " +
						"where photo_id IN (select t.tag_photo_id from " + tagTableName + " t " +
						"where t.tag_photo_id = photo_id AND t.tag_subject_id = " + uid1 + " AND " +
						"EXISTS(select t2.tag_photo_id from " + tagTableName + " t2 " +
						"where t2.tag_photo_id = photo_id AND t2.tag_subject_id = " + uid2 + "))");



			while(photoInfo.next())
			{
                        	String sharedPhotoId = photoInfo.getString(1);
				String sharedPhotoAlbumId = photoInfo.getString(2);
				String sharedPhotoCaption = photoInfo.getString(3);
				String sharedPhotoLink = photoInfo.getString(4);

				albumInfo = stmt4.executeQuery("select album_name from " + albumTableName + 
					" where album_id = " + sharedPhotoAlbumId);
			
				albumInfo.next();

		   		String sharedPhotoAlbumName = albumInfo.getString(1);     	
				mp.addSharedPhoto(new PhotoInfo(sharedPhotoId, sharedPhotoAlbumId, 
		      			sharedPhotoAlbumName, sharedPhotoCaption, sharedPhotoLink));
			}

			this.bestMatches.add(mp);

                	n++;
		}
         }

	
	
	// **** Query 6 ****
	// Suggest friends based on mutual friends
	// 
	// Find the top n pairs of users in the database who share the most
	// friends, but such that the two users are not friends themselves.
	//
	// Your output will consist of a set of pairs (user1_id, user2_id)
	// No pair should appear in the result twice; you should always order the pairs so that
	// user1_id < user2_id
	//
	// If there are ties, you should give priority to the pair with the smaller user1_id.
	// If there are still ties, give priority to the pair with the smaller user2_id.
	//
	@Override
	public void suggestFriendsByMutualFriends(int n) throws SQLException {
		Statement stmt2 = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        //provides length of last name, last name
		//ln last name
		//fn first name
		//last line orders and makes sure not already in friends, and not the same
		//A is first user, find where A shows up as user1 or user2 (same with b)
		//And the opposite friend from each table equals (a.friend =b.friend)
		//order by column 1 ( cnt)
		
		//finally only choose 2*n
        ResultSet rst = stmt.executeQuery("select cnt, usr1, usr2, fn1, ln1, fn2, ln2 from" 
                +" (select count(*) as cnt, C.usr1, c.usr2, C.fn1, C.ln1, C.fn2, C.ln2 from "
                + "(select A.usr as usr1, A.first_Name as fn1, A.last_Name as ln1 , B.usr as usr2, B.first_Name as fn2, B.last_Name as ln2 from " 
                + "(select P.user_id as usr, P.first_Name, P.last_Name , F.user2_id as friend from "+userTableName+" P, "+friendsTableName+" F  " + 
            "where P.user_id=F.user1_id "
            + "UNION select P.user_id as usr,P.first_Name, P.last_Name, F.user1_id as friend from "+userTableName+" P, "+friendsTableName+" F "+
            "where P.user_id=F.user2_id)A, " +
            "(select P.user_id as usr, P.first_Name, P.last_Name ,F.user2_id as friend from "+userTableName+" P, "+friendsTableName+" F "+ 
            "where P.user_id=F.user1_id "
            + "UNION select P.user_id as usr, P.first_Name, P.last_Name, F.user1_id as friend from "+userTableName+" P, "+friendsTableName+" F "+
            "where P.user_id=F.user2_id)B " + 
            "where A.usr<>B.usr " 
            + "AND A.friend=B.friend)C group by C.usr1, C.usr2, C.fn1, C.ln1, C.fn2, C.ln2 order by 1 desc)E " +
            "where usr1<usr2 AND  (usr1, usr2) not in (select user1_id, user2_id from "+friendsTableName+") AND (usr2, usr1) not in (select user1_id, user2_id from "+friendsTableName+") AND rownum<="+(2*n)+ 
            " order by cnt desc, usr1 asc, usr2 asc");

        
        int counter=0;
        //loop through the friends
        while(rst.next() && counter<n) 
        {        

            int count = rst.getInt(1);
            Long id1 = rst.getLong(2);
            Long id2 = rst.getLong(3);
            String fn1 = rst.getString(4);
            String ln1 = rst.getString(5);
            String fn2 = rst.getString(6);
            String ln2 = rst.getString(7);
        
            FriendsPair p = new FriendsPair(id1, fn1, ln1, id2, fn2, ln2);//make a pair
				//basically subquery from above, get all the friends they have in common from previous query (since already ordered by greatest count)
                ResultSet rst2 = stmt2.executeQuery("select distinct Z.user_id, Z.first_name, Z.last_name from " 
                       + "(select P.user_id as usr, F.user2_id as friend from "+userTableName+" P, "+friendsTableName+" F "
                    + "where P.user_id=F.user1_id    UNION    select P.user_id as usr, F.user1_id as friend from "+userTableName+" P, "+friendsTableName+" F "  
                    + " where P.user_id=F.user2_id)A, (select P.user_id as usr, F.user2_id as friend    from "+userTableName+" P, "+friendsTableName+" F " 
                    + "where P.user_id=F.user1_id    UNION    select P.user_id as usr, F.user1_id as friend    from "+userTableName+" P, "+friendsTableName+" F "
                    + "where P.user_id=F.user2_id)B, "+userTableName+" Z " 
                    + "where A.usr<>B.usr AND A.friend=B.friend AND A.usr="+id1+" AND B.usr="+id2+" AND Z.user_id=A.friend");
			
			//add all the suggested pairs to the shared friend
            for(int j=0; j<count; j++)//add the pair
            {
                
                rst2.next();

                Long id = rst2.getLong(1);
                String first = rst2.getString(2);
                String last = rst2.getString(3);
                p.addSharedFriend(id, first, last);    

            }
            this.suggestedFriendsPairs.add(p);
            counter++;
        }
        rst.close();
        stmt.close();

    }

	//@Override
	// ***** Query 7 *****
	// Given the ID of a user, find information about that
	// user's oldest friend and youngest friend
	// 
	// If two users have exactly the same age, meaning that they were born
	// on the same day, then assume that the one with the larger user_id is older
	//
	public void findAgeInfo(Long user_id) throws SQLException {
		Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rst;
		
        /*from all friends, find oldest
         * must do union because the repeats aren't stored so you have to look at user1 and user2
         * second outer most gives sorted list and outer most takes the top
         * */
        rst = stmt.executeQuery(
                "SELECT u.user_id, u.first_name, u.last_name FROM" +
                " (SELECT u.user_id, u.first_name, u.last_name" +
                "  FROM " + userTableName + " u, " +
                "     ((SELECT user2_id friend_id FROM " + friendsTableName + " WHERE user1_id = "+user_id+") UNION" +
                "      (SELECT user1_id friend_id FROM " + friendsTableName + " WHERE user2_id = "+user_id+")" +
                "      ) f" +
                "  WHERE u.user_id = f.friend_id" +
                "  ORDER BY u.year_of_birth ASC, u.month_of_birth ASC, u.day_of_birth ASC, u.user_id DESC) u" +
                " WHERE ROWNUM <= 1"
        );
       
        if(rst.next()) {
            this.oldestFriend = new UserInfo(rst.getLong(1), rst.getString(2), rst.getString(3));
        }
	    
       /*from all friends, find youngest*/	
        rst = stmt.executeQuery(
                "SELECT u.user_id, u.first_name, u.last_name FROM" +
                " (SELECT u.user_id, u.first_name, u.last_name" +
                "  FROM " + userTableName + " u, " +
                "     ((SELECT user2_id friend_id FROM " + friendsTableName + " WHERE user1_id = "+user_id+") UNION" +
                "      (SELECT user1_id friend_id FROM " + friendsTableName + " WHERE user2_id = "+user_id+")" +
                "      ) f" +
                "  WHERE u.user_id = f.friend_id" +
                "  ORDER BY u.year_of_birth DESC, u.month_of_birth DESC, u.day_of_birth DESC, u.user_id ASC) u" +
                " WHERE ROWNUM <= 1"
        );

        if(rst.next()) {
            this.youngestFriend = new UserInfo(rst.getLong(1), rst.getString(2), rst.getString(3));
        }
        
        rst.close();
        stmt.close();
	}
	
	
	@Override
	// ***** Query 8 *****
	// 
	// Find the name of the city with the most events, as well as the number of 
	// events in that city.  If there is a tie, return the names of all of the (tied) cities.
	//
	public void findEventCities() throws SQLException {
		Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        /* get the max count, select city names from those with max */
		ResultSet rst = stmt.executeQuery(
                    "SELECT COUNT(e.event_id), c.city_name"+
                    " FROM " + eventTableName + " e, " + cityTableName + " c" +
                    " WHERE e.event_city_id = c.city_id" +
                    " GROUP BY c.city_name" +
                    " HAVING COUNT(e.event_id) = (SELECT MAX(COUNT(*)) FROM " + eventTableName + " GROUP BY event_city_id)" 
                    );
        
        /*add each city, the number is the same for each*/
        while(rst.next()) {
            this.eventCount = rst.getInt(1);
            this.popularCityNames.add(rst.getString(2));
        }

        rst.close();
        stmt.close();
	}
	
	
	
	@Override
	//***** Query 9 *****
	//
	// Find pairs of potential siblings and print them out in the following format:
	//   # pairs of siblings
	//   sibling1 lastname(id) and sibling2 lastname(id)
	//   siblingA lastname(id) and siblingB lastname(id)  etc.
	//
	// A pair of users are potential siblings if they have the same last name and hometown, if they are friends, and
	// if they are less than 10 years apart in age.  Pairs of siblings are returned with the lower user_id user first
	// on the line.  They are ordered based on the first user_id and in the event of a tie, the second user_id.
	//  
	//
	public void findPotentialSiblings() throws SQLException {

                
		Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		        ResultSet.CONCUR_READ_ONLY);

		ResultSet siblings = stmt.executeQuery("select u1.user_id, u2.user_id, u1.first_name, u2.first_name, u1.last_name, u2.last_name " +
						"from " + userTableName + " u1, " + userTableName + " u2 " +
					        "where u1.last_name = u2.last_name AND ABS(u1.year_of_birth - u2.year_of_birth) < 10 AND " +
						"EXISTS(select t.user1_id from " + friendsTableName + " t where t.user1_id = u1.user_id AND t.user2_id = u2.user_id) AND " +
					        "EXISTS(select h1.user_id, h2.user_id from " + hometownCityTableName + " h1, " + hometownCityTableName + " h2 " +
					        "where h1.user_id = u1.user_id AND h2.user_id = u2.user_id AND h1.hometown_city_id = h2.hometown_city_id) " +
					        "order by u1.user_id asc, u2.user_id asc");

		while(siblings.next())
		{
                 	Long user1_id = siblings.getLong(1);
			Long user2_id = siblings.getLong(2);
			String user1FirstName = siblings.getString(3);
			String user2FirstName = siblings.getString(4);
			String user1LastName = siblings.getString(5);
			String user2LastName = siblings.getString(6);
			SiblingInfo s = new SiblingInfo(user1_id, user1FirstName, user1LastName, user2_id, user2FirstName, user2LastName);
			this.siblings.add(s);
		}
		
	}
	
}














